package export

import (
	"context"
	"database/sql"
	"fmt"
	"mysqltools/config"
	"mysqltools/mysql"
	"strings"
	"time"
)

type MapSS mysql.MapSS

type ServerInfo struct {
	Version string
}

type Table struct {
	Name    string
	DDL     string
	Comment string
	Fields  MapSS
	Values  string
}
type DataBases struct {
	Name   string
	DDL    string
	Tables []string
	Tri    []string
	Func   []string
	Proc   []string
	Event  []string
	View   MapSS
	Info   ServerInfo
}

type exporter struct {
	opera mysql.Operator
	gen   *Genera
	conn  *sql.Conn
}

const (
	// head set
	showVersion = "SELECT VERSION();"

	// %s - DB
	cmdUseDB      = "USE %s"
	cmdCreateDB   = "SHOW CREATE DATABASE IF NOT EXISTS `%s`"
	cmdShowTables = "SHOW TABLES"

	//templeTrigger   = "DELIMITER $$\n/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `%s` */$$\n/*!50003 CREATE */ /*!50017 DEFINER = %s */ \n/*!50003 TRIGGER `%s`.`%s` %s %s ON `%s`.`%s` FOR EACH ROW %s*/$$\n\nDELIMITER ;\n\n"
	// DB -- function
	cmdShowFunctions  = "SHOW FUNCTION STATUS WHERE DB = '%s'"
	cmdShowCreateFunc = "SHOW CREATE FUNCTION `%s`" // function Name
	// DB -- Procedure
	cmdShowProcedure  = "SHOW PROCEDURE STATUS WHERE DB = '%s'"
	cmdShowCreateProc = "SHOW CREATE PROCEDURE `%s`" // Procedure Name
	// view
	//cmdSelectViews    = "SELECT `TABLE_NAME` FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA` = ? AND `TABLE_TYPE` = 'VIEW'; "
	//cmdShowCreateView = "SHOW CREATE VIEW `%s`"
	// event
	cmdShowEvent       = "SHOW EVENTS FROM `%s`"
	cmdShowCreateEvent = "SHOW CREATE EVENT `%s`"
	// %s - table name
	cmdShowCreateTable = "SHOW CREATE TABLE `%s`"
	cmdShowTableStatus = "SHOW TABLE STATUS LIKE '%s'"
	cmdShowFields      = "SHOW FIELDS FROM `%s`"
	cmdSelectValues    = "SELECT /*!40001 SQL_NO_CACHE */ * FROM `%s`"
	// table -- Triggers
	cmdShowTriggers    = "SHOW TRIGGERS LIKE '%s'"
	setCharacterBinary = "SET SESSION character_set_results = 'binary'"
	setCharacterUtf8   = "SET SESSION character_set_results = 'utf8'"
	setSavePoint       = "SAVEPOINT sp"             // 创建一个检查点，检查点的作用是在一个事务中执行ROLLBACK TO SAVEPOINT语句之后能够将事务回滚到检查点位置而不中止事务
	setROLLBACK        = "ROLLBACK TO SAVEPOINT sp" // 回滚到检查点
	setRelease         = "RELEASE SAVEPOINT sp"     // 释放检查点

)

func ExportDB(conf config.DBConfig, option *Option) {
	fmt.Println("Export ", "\t start at \t", time.Now().Format("2006-01-02 15:04:05"))
	gen := Genera{op: option}
	gen.DataCH = make(chan string, 10)
	gen.DoneCH = make(chan bool, 1)
	if gen.op.AllData {
		gen.op.IsEvent = true
		gen.op.IsFunc = true
		gen.op.IsTrig = true
		gen.op.IsProc = true
	}

	fullPath := option.Path + "/" + conf.Database + "_" + time.Now().Format("2006-01-02") + ".sql"
	connect := mysql.Connection{}
	conn, _ := connect.Connect(conf)

	go WriteToFile(fullPath, gen.DataCH, gen.DoneCH)
	go exportProc(conf.Database, &gen, conn)
	<-gen.DoneCH
	fmt.Println("Export ", "\t end at \t", time.Now().Format("2006-01-02 15:04:05"))
}

func exportProc(dbName string, gen *Genera, conn *sql.Conn) {
	var opera mysql.Operator = &mysql.ConnDB{
		Conn: conn,
		CTX:  context.TODO(),
	}
	ex := exporter{gen: gen, opera: opera, conn: conn}
	defer ex.close()

	db := &DataBases{Name: dbName}
	ex.showVersion(db)
	ex.execHeadCommand()
	ex.showCreateDB(db)
	err := ex.opera.ExecControl(setSavePoint)
	if err != nil {
		return
	}
	ex.gen.GenFront(db)
	ex.showTables(db)
	//fmt.Println(db)
	for i := range db.Tables {
		table := &Table{Name: db.Tables[i]}
		ex.showTableStatus(table)
		_ = ex.opera.ExecControl(setCharacterBinary)
		if table.Comment == "VIEW" {
			ex.showCreateView(db, db.Tables[i])
			continue
		}
		ex.showCreateTable(table)
		ex.gen.SQLTable(table)
		_ = ex.opera.ExecControl(setCharacterUtf8)

		ex.showFields(table)
		ex.selectValuesToString(table)
		// trig
		if ex.gen.op.IsTrig {
			_ = ex.opera.ExecControl(setCharacterBinary)
			ex.showTriggers(db, table)
			_ = ex.opera.ExecControl(setCharacterUtf8)
		}
		ex.gen.SQLTableEnd(table)
	}

	err = ex.opera.ExecControl(setROLLBACK)
	if err != nil {
		return
	}
	_ = ex.opera.ExecControl(setRelease)

	// func
	if ex.gen.op.IsFunc {
		ex.showFunction(db)
		for i := range db.Func {
			ex.showCreatFuncToString(db.Func[i])
		}
	}
	// proc
	if ex.gen.op.IsProc {
		ex.showProcedure(db)
		for i := range db.Proc {
			ex.showCreatProcToString(db.Proc[i])
		}
	}
	// event
	if ex.gen.op.IsEvent {
		ex.showEvent(db)
		for i := range db.Event {
			ex.showCreatEventToString(db.Event[i])
		}
	}
	// view
	for k, v := range db.View {
		ex.gen.SQLView(k, v)
	}

	ex.gen.GenEnd()
	close(ex.gen.DataCH)
}

func (ex *exporter) execHeadCommand() {
	err := ex.opera.ExecControl("/*!40100 SET @@SQL_MODE='' */")
	err = ex.opera.ExecControl("/*!40103 SET TIME_ZONE='+00:00' */")
	err = ex.opera.ExecControl("SET NAMES UTF8")
	err = ex.opera.ExecControl("FLUSH /*!40101 LOCAL */ TABLES")                          // 关闭所有的表，并强制关闭所有正在使用的表，同时也会移除所有query cache结果。
	err = ex.opera.ExecControl("FLUSH TABLES WITH READ LOCK")                             // 关闭所有的表，并强制关闭所有正在使用的表，同时也会移除所有query cache结果。
	err = ex.opera.ExecControl("SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ") // 设置会话隔离级别为RR (REPEATABLE READ)
	err = ex.opera.ExecControl("START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */")  // 开启一致性快照事务,因为要开启一致性快照事务，所以必须将务事务的隔离级别设置成RR
	err = ex.opera.ExecControl("UNLOCK TABLES")
	err = ex.opera.ExecControl("SET NAMES UTF8")
	err = ex.opera.ExecControl("SET NAMES UTF8")
	err = ex.opera.ExecControl("SET NAMES UTF8")
	if err != nil {
		return
	}
}
func (ex *exporter) exeEndCommand() {
	err := ex.opera.ExecControl(setRelease)
	if err != nil {
		return
	}
}

func (ex *exporter) showVersion(db *DataBases) {
	row, err := ex.opera.QueryRows(showVersion)
	if err != nil {
		return
	}
	if len(row) > 1 {
		return
	}
	db.Info.Version = row[0]["VERSION()"]
}
func (ex *exporter) showCreateDB(db *DataBases) {
	sqlStr_ := fmt.Sprintf(cmdUseDB, db.Name)
	err := ex.opera.ExecControl(sqlStr_)
	if err != nil {
		return
	}
	sqlStr := fmt.Sprintf(cmdCreateDB, db.Name)
	rows, err := ex.opera.QueryRows(sqlStr)
	if err != nil {
		return
	}
	if len(rows) > 1 {
		return
	}
	db.DDL = rows[0]["Create Database"]
}

func (ex *exporter) showTables(db *DataBases) {
	sqlStr := fmt.Sprintf(cmdShowTables)
	rows, err := ex.opera.QueryRows(sqlStr)
	if err != nil {
		return
	}
	for _, row := range rows {
		db.Tables = append(db.Tables, row["Tables_in_"+db.Name])
	}
}

func (ex *exporter) showTriggers(db *DataBases, table *Table) {
	sqlStr := fmt.Sprintf(cmdShowTriggers, table.Name)
	rows, err := ex.opera.QueryRows(sqlStr)
	if err != nil {
		return
	}

	for _, row := range rows {
		definer := strings.Replace(row["Definer"], "@", "'@'", -1)
		sqlTRI := "DELIMITER ;;\n/*!50003 CREATE */ /*!50017 DEFINER ='" + definer +
			"' */ /*!50003 TRIGGER `" + db.Name + "`.`" + row["Table"] + "` " + row["Timing"] + " " + row["Event"] +
			" ON `" + db.Name + "`.`" + row["Table"] + "` FOR EACH ROW " + row["Statement"] +
			" */;;\nDELIMITER ;\n\n"
		db.Tri = append(db.Tri, sqlTRI)
		ex.gen.sqlDataTemple(sqlTRI)
	}
}

func (ex *exporter) showFunction(db *DataBases) {
	sqlStr := fmt.Sprintf(cmdShowFunctions, db.Name)
	rows, err := ex.opera.QueryRows(sqlStr)
	if err != nil {
		return
	}
	for i := range rows {
		db.Func = append(db.Func, rows[i]["Name"])
	}
}

func (ex *exporter) showProcedure(db *DataBases) {
	sqlStr := fmt.Sprintf(cmdShowProcedure, db.Name)
	rows, err := ex.opera.QueryRows(sqlStr)
	if err != nil {
		return
	}
	for i := range rows {
		db.Proc = append(db.Proc, rows[i]["Name"])
	}
}

func (ex *exporter) showEvent(db *DataBases) {
	sqlStr := fmt.Sprintf(cmdShowEvent, db.Name)
	rows, err := ex.opera.QueryRows(sqlStr)
	if err != nil {
		return
	}
	for i := range rows {
		db.Event = append(db.Event, rows[i]["Name"])
	}
}

func (ex *exporter) showCreateView(db *DataBases, name string) {
	sqlStr := fmt.Sprintf(cmdShowCreateTable, name)
	row, err := ex.opera.QueryRows(sqlStr)
	if err != nil {
		return
	}
	if len(row) > 1 {
		return
	}
	db.View[name] = row[0]["Create View"]
}

func (ex *exporter) showTableStatus(table *Table) {
	sqlStr := fmt.Sprintf(cmdShowTableStatus, table.Name)
	row, err := ex.opera.QueryRows(sqlStr)
	if err != nil {
		return
	}
	if len(row) > 1 {
		return
	}
	table.Comment = row[0]["Comment"]
}

func (ex *exporter) showCreateTable(table *Table) {
	sqlStr := fmt.Sprintf(cmdShowCreateTable, table.Name)
	row, err := ex.opera.QueryRows(sqlStr)
	if err != nil {
		return
	}
	if len(row) > 1 {
		return
	}
	table.DDL = row[0]["Create Table"]
}

func (ex *exporter) showFields(table *Table) {
	sqlStr := fmt.Sprintf(cmdShowFields, table.Name)
	rows, err := ex.opera.QueryRows(sqlStr)
	if err != nil {
		return
	}
	table.Fields = make(MapSS)
	for _, row := range rows {
		table.Fields[row["Field"]] = row["Type"]
	}
}

func (ex *exporter) selectValuesToString(table *Table) {
	sqlStr := fmt.Sprintf(cmdSelectValues, table.Name)
	db := ex.opera.GetDB()
	rows, err := db.Conn.QueryContext(db.CTX, sqlStr)
	if err != nil {
		return
	}
	//读出查询出的列字段名
	cols, _ := rows.Columns()
	if len(cols) == 0 {
		return
	}
	//values是每个列的值，这里获取到byte里
	values := make([][]byte, len(cols))
	//query.Scan的参数，因为每次查询出来的列是不定长的，用len(cols)定住当次查询的长度
	scans := make([]interface{}, len(cols))
	//让每一行数据都填充到[][]byte里面,狸猫换太子
	for i := range values {
		scans[i] = &values[i]
	}
	var valuerBuilder strings.Builder
	valuerBuilder.Grow(1024 * 129)
	for rows.Next() {
		err := rows.Scan(scans...)
		if err != nil {
			return
		}
		n := len(",") * (len(values) - 1)
		for _, v := range values {
			n += len(v)
		}
		var b strings.Builder
		b.Grow(n)
		b.WriteString("(")
		b.WriteString(mysql.ConvertValueToString(table.Fields[cols[0]], values[0]))
		for i, v := range values[1:] {
			key := cols[i+1]
			b.WriteString(",")
			b.WriteString(mysql.ConvertValueToString(table.Fields[key], v))
		}
		b.WriteString(")")
		if valuerBuilder.Len() == 0 {
			valuerBuilder.WriteString(b.String())
		} else {
			valuerBuilder.WriteString(",")
			valuerBuilder.WriteString(b.String())
		}
		// 参考SQL yong 超过1024*128 则重新生成一天insert语句
		if valuerBuilder.Len() > 1024*128 {
			valuerBuilder.WriteString(";\n")
			ex.gen.SQLInsert(table.Name, valuerBuilder.String())
			valuerBuilder.Reset()
			valuerBuilder.Grow(1024 * 129)
		}
	}
	if valuerBuilder.Len() > 0 {
		valuerBuilder.WriteString(";\n")
		ex.gen.SQLInsert(table.Name, valuerBuilder.String())
	}

}

func (ex *exporter) showCreatFuncToString(funcName string) {
	sqlStr := fmt.Sprintf(cmdShowCreateFunc, funcName)
	rows, err := ex.opera.QueryRows(sqlStr)
	if err != nil {
		return
	}
	for _, row := range rows {
		ex.gen.SQLFunc(funcName, row["Create Function"])
	}
}

func (ex *exporter) showCreatProcToString(procName string) {
	sqlStr := fmt.Sprintf(cmdShowCreateProc, procName)
	rows, err := ex.opera.QueryRows(sqlStr)
	if err != nil {
		return
	}
	for _, row := range rows {
		ex.gen.SQLProc(procName, row["Create Procedure"])
	}
}

func (ex *exporter) showCreatEventToString(eventName string) {
	sqlStr := fmt.Sprintf(cmdShowCreateEvent, eventName)
	rows, err := ex.opera.QueryRows(sqlStr)
	if err != nil {
		return
	}
	for _, row := range rows {
		ex.gen.SQLEvent(eventName, row["Create Event"])
	}
}

func (ex *exporter) close() {
	err := ex.opera.Close()
	if err != nil {
		return
	}
}
